package br.unipampa.dados;

import br.unipampa.view.ApoioDeInterface;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * @author Gabriel Moro
 * @since 15/03/2013
 * @version 1.2
 */
public class DBCompromisso extends DML {

    private ArrayList<Object> preElementos;
    private ArrayList<Object> elementos;

    public DBCompromisso(Connection con) {
        super.connection = (com.mysql.jdbc.Connection) con;
        super.table = "compromissos";
    }

    @Override
    public ArrayList<Object> select(String coluna) {
        return null;
    }

    public ArrayList<Integer> selectEstatistica(String ano, String mes) {
        ArrayList<Integer> estatistica = new ArrayList<>();
        int codAno = 0;
        int codMes = 0;

        codAno = validationCodigoAno(ano);
        codMes = validationCodigoMes(mes);
        try {
            super.statement = (com.mysql.jdbc.Statement) super.connection.createStatement();
            super.resultSet = super.resultSet = super.statement.executeQuery("SELECT codDia_compromisso FROM " + super.table + " WHERE codMes_compromisso= " + codMes + " and codAno_compromisso= " + codAno + " ORDER BY codDia_compromisso ASC;");

            while (super.resultSet.next()) {
                int date = validationDate(super.resultSet.getInt(1));
                if (date != 0) {
                    if (!estatistica.contains(date)) {
                        estatistica.add(date);
                    }
                }
            }
            return estatistica;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return null;
    }
    //definicao_compromisso,codAno_compromisso,codMes_compromisso,codDia_compromisso,codHora_compromisso
    public ArrayList<Object> selected(String coluna, int dia, String mes, String ano) {
        try {
            this.preElementos = new ArrayList<>();
            this.elementos = new ArrayList<>();

            super.statement = (com.mysql.jdbc.Statement) super.connection.createStatement();
            super.resultSet = super.resultSet = super.statement.executeQuery("SELECT " + coluna + " FROM " + super.table + ";");

            int codAno = 0;
            int codMes = 0;
            int codDia = 0;

            while (super.resultSet.next()) {
                String comp = super.resultSet.getString(1);
                codAno = super.resultSet.getInt(2);
                codMes = super.resultSet.getInt(3);
                codDia = super.resultSet.getInt(4);

                if (validationAno(codAno).equals(ano)) {
                    if (validationMes(codMes).equals(mes)) {
                        if (validationDate(codDia) == dia) {
                            this.preElementos.add(codAno);
                            this.preElementos.add(codMes);
                            this.preElementos.add(codDia);
                        }
                    }
                }
            }
            super.resultSet.close();

            for (int x = 0; x < this.preElementos.size(); x = x + 3) {
                if (this.preElementos.get(x) != null && this.preElementos.get(x + 1) != null
                        && this.preElementos.get(x + 2) != null) {
                    super.resultSet = super.statement.executeQuery("SELECT codHora_compromisso,definicao_compromisso FROM " + super.table
                            + " WHERE codAno_compromisso= " + this.preElementos.get(x) + " and codMes_compromisso= "
                            + this.preElementos.get(x + 1) + " and codDia_compromisso= "
                            + this.preElementos.get(x + 2) + " ORDER BY codHora_compromisso ASC;");

                    while (super.resultSet.next()) {
                        String hora = validationHora(super.resultSet.getInt(1));
                        String compromisso = super.resultSet.getString(2);
                        if (this.elementos.contains(hora) == false) {
                            this.elementos.add(hora);
                            this.elementos.add(compromisso);
                        }
                    }
                }
                super.resultSet.close();
            }
            return this.elementos;

        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return null;
    }
        
    public int insert(String compromisso, int dia, String hora, String mes, String ano) {
        try {
            int codHora = validationCodigoHora(hora);
            int codDia = validationCodigoDate(dia);
            int codMes = validationCodigoMes(mes);
            int codAno = validationCodigoAno(ano);
            
            if (verificaExistenciaDeRegistro(codHora,codDia,codMes,codAno)) {
                  ApoioDeInterface.mensagemCompromissoExistente();
            } else {
                super.statement = (com.mysql.jdbc.Statement) super.connection.createStatement();
                int registrosAfetados = super.statement.executeUpdate("INSERT INTO " + super.table
                        + "(definicao_compromisso,codDia_compromisso,codHora_compromisso,codMes_compromisso,codAno_compromisso) " + " VALUES ("
                        + "'" + compromisso + "'," + codDia + "," + codHora + "," + codMes + "," + codAno + ");");

                return registrosAfetados;
            }
            
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return 0;
    }

    @Override
    public int update(String atributoX, String valores) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public int updated(String compromisso, int dia, String hora, String mes, String ano) {
        try {
            int codDia = validationCodigoDate(dia);
            int codHora = validationCodigoHora(hora);
            int codMes = validationCodigoMes(mes);
            int codAno = validationCodigoAno(ano);

            super.statement = (com.mysql.jdbc.Statement) super.connection.createStatement();
            int registrosAfetados = super.statement.executeUpdate("UPDATE "
                    + super.table + " SET definicao_compromisso="
                    + "'" + compromisso + "'" + " WHERE codHora_compromisso= " + codHora
                    + " and codMes_compromisso= " + codMes + " and " + "codAno_compromisso= " + codAno
                    + " and codDia_compromisso= " + codDia + ";");
            return registrosAfetados;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return 0;
    }

    @Override
    public int delete(String valor) {
        try {
            int codHora = validationCodigoHora(valor);

            super.statement = (com.mysql.jdbc.Statement) super.connection.createStatement();
            int registrosAfetados = super.statement.executeUpdate("DELETE FROM " + super.table + " WHERE codHora_compromisso= " + codHora + ";");
            return registrosAfetados;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return 0;
    }

    @Override
    public void closedConnection() {
        try {
            super.resultSet.close();
            super.statement.close();
            super.connection.close();
        } catch (SQLException erroSQl) {
            ApoioDeInterface.mensagemErroBanco(erroSQl.getMessage());
        }
    }

    private int validationDate(int codigo) {
        try {
            Statement stm = (com.mysql.jdbc.Statement) super.connection.createStatement();
            ResultSet rs = stm.executeQuery("SELECT numero_dia" + " FROM dias WHERE codigo_dias= " + codigo + ";");
            int value = 0;
            while (rs.next()) {
                value = rs.getInt(1);
            }
            stm.close();
            rs.close();
            return value;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
            return 0;
        }
    }

    private int validationCodigoDate(int data) {
        try {
            Statement stm = (com.mysql.jdbc.Statement) super.connection.createStatement();
            ResultSet rs = stm.executeQuery("SELECT codigo_dias" + " FROM dias WHERE numero_dia= " + data + ";");
            int value = 0;
            while (rs.next()) {
                value = rs.getInt(1);
            }
            stm.close();
            rs.close();
            return value;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
            return 0;
        }
    }

    private String validationMes(int codigo) {
        try {
            Statement stm = (com.mysql.jdbc.Statement) super.connection.createStatement();
            ResultSet rs = stm.executeQuery("SELECT nome_meses" + " FROM meses WHERE codigo_meses= " + codigo + ";");
            String value = null;
            while (rs.next()) {
                value = rs.getString(1);
            }
            stm.close();
            rs.close();
            return value;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return null;
    }

    private int validationCodigoMes(String mes) {
        try {
            Statement stm = (com.mysql.jdbc.Statement) super.connection.createStatement();
            ResultSet rs = stm.executeQuery("SELECT codigo_meses" + " FROM meses WHERE nome_meses= '" + mes + "';");
            int value = 0;
            while (rs.next()) {
                value = rs.getInt(1);
            }
            stm.close();
            rs.close();
            return value;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return 0;
    }

    private String validationAno(int codigo) {
        try {
            Statement stm = (com.mysql.jdbc.Statement) super.connection.createStatement();
            ResultSet rs = stm.executeQuery("SELECT nome_anos" + " FROM anos WHERE codigo_anos= " + codigo + ";");
            String value = null;
            while (rs.next()) {
                value = rs.getString(1);
            }
            stm.close();
            rs.close();
            return value;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return null;
    }

    private int validationCodigoAno(String ano) {
        try {
            Statement stm = (com.mysql.jdbc.Statement) super.connection.createStatement();
            ResultSet rs = stm.executeQuery("SELECT codigo_anos" + " FROM anos WHERE nome_anos= '" + ano + "';");
            int value = 0;
            while (rs.next()) {
                value = rs.getInt(1);
            }
            stm.close();
            rs.close();
            return value;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return 0;
    }

    private String validationHora(int codigo) {
        try {
            Statement stm = (com.mysql.jdbc.Statement) super.connection.createStatement();
            ResultSet rs = stm.executeQuery("SELECT valor_horas" + " FROM horas WHERE codigo_horas= " + codigo + ";");
            String value = null;
            while (rs.next()) {
                value = rs.getString(1);
            }
            stm.close();
            rs.close();
            return value;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return null;
    }

    private int validationCodigoHora(String hora) {
        try {
            Statement stm = (com.mysql.jdbc.Statement) super.connection.createStatement();
            ResultSet rs = stm.executeQuery("SELECT codigo_horas" + " FROM horas WHERE valor_horas= '" + hora + "';");
            int value = 0;
            while (rs.next()) {
                value = rs.getInt(1);
            }
            stm.close();
            rs.close();
            return value;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return 0;
    }

    @Override
    public int insert(String valores) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean verificaExistenciaDeRegistro(int codHora,int codDia,int codMes,int codAno) {
        try {
            super.statement = (com.mysql.jdbc.Statement) super.connection.createStatement();
            super.resultSet = super.statement.executeQuery("SELECT definicao_compromisso" + " FROM compromissos WHERE codHora_compromisso= " + codHora +
                    " and codDia_compromisso= "+codDia+ " and codMes_compromisso= "+
                    codMes+" and codAno_compromisso= "+ codAno+ ";");
            
            ArrayList<String> value = new ArrayList<>();
            while (super.resultSet.next()) {
                value.add(super.resultSet.getString(1));
            }
            if (value.isEmpty()) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return false;
    }
}
